package se.djupfeldt.oscar.logbook.database;

import java.io.File;
import java.util.logging.Level;
import java.util.logging.Logger;

import se.djupfeldt.oscar.logbook.Logbook;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteStatement;

public class DatabaseHandler {
    public enum DB_RESULT {
	INIT_OK_DB_EXISTS, INIT_OK_DB_CREATED, INIT_ERROR, INSERT_OK, INSERT_FAILED, SELECT_FAILED, SELECT_OK
    }
    
    private static DatabaseHandler instance;
    private org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(DatabaseHandler.class);
    private String dbFile;
    
    public static final String[] tables = {"gps_position", "gps_fix", "gyro", "light"};
    
    private DatabaseHandler() {
	dbFile = Logbook.properties.getProperty("database.file");
    }
    
    public static DatabaseHandler getInstance() {
	if (instance == null)
	    instance = new DatabaseHandler();
	return instance;
    }
    
    public DB_RESULT init() {
	return init(false);
    }
    
    public DB_RESULT init(boolean reset) {
	log.info("Initializing database: " + dbFile);
	Logger.getLogger("com.almworks.sqlite4java").setLevel(Level.OFF);
	File dbf = new File(dbFile);
	
	if (reset) {
	    if (dbf.delete()) {
		log.info("Database file " + dbFile + " deleted.");
	    } else {
		log.error("Failed to delete database file " + dbFile + ".");
		return DB_RESULT.INIT_ERROR;
	    }
	}
	
	if (!dbf.exists()) {
	    log.info("Database does not exist. Creating database.");
	    SQLiteConnection db = new SQLiteConnection(dbf);
	    try {
		db.open();
	    } catch (SQLiteException e) {
		log.error("Error while opening database", e);
		return DB_RESULT.INIT_ERROR;
	    }
	    for (String table : tables) {
		if (!createTable(db, table)) {
		    return DB_RESULT.INIT_ERROR;
		}
	    }
	    
	    db.dispose();
	    
	    return DB_RESULT.INIT_OK_DB_CREATED;
	} else {
	    log.info("Database exist. Opening database.");
	    SQLiteConnection db = new SQLiteConnection(dbf);
	    try {
		db.open();
	    } catch (SQLiteException e) {
		log.error("Error while opening database", e);
		return DB_RESULT.INIT_ERROR;
	    }
	    return DB_RESULT.INIT_OK_DB_EXISTS;
	}
    }

    private boolean createTable(SQLiteConnection db, String table) {
	String create = "CREATE TABLE " + table + " (" +
		    "sentence VARCHAR(100) NOT NULL" +
		    ")";
	try {
	    db.exec(create);
	    log.info(table + " table created.");
	    return true;
	} catch (SQLiteException e) {
	    db.dispose();
	    log.error("Error while creating " + table + " table", e);
	    return false;
	}
    }
    
    public DB_RESULT insert(String table, String sentence) {
	File dbf = new File(dbFile);
	SQLiteConnection db = new SQLiteConnection(dbf);
	try {
	    db.open();
	} catch (SQLiteException e) {
	    log.error("Error while opening database for insertion", e);
	    return DB_RESULT.INSERT_FAILED;
	}
	String insert = "INSERT INTO " + table + " (sentence) VALUES (\"" + sentence + "\")";
	try {
	    db.exec(insert);
	} catch (SQLiteException e) {
	    log.error("Error while trying to insert sentence: " + insert, e);
	    return DB_RESULT.INSERT_FAILED;
	}
	
	return DB_RESULT.INSERT_OK;
    }
    
    public DB_RESULT selectAndPrint(String query) {
	if (query.toLowerCase().startsWith("select ")) {
	    query = query.substring(7);
	}
	File dbf = new File(dbFile);
	SQLiteConnection db = new SQLiteConnection(dbf);
	try {
	    db.open();
	} catch (SQLiteException e) {
	    log.error("Error while opening database for selection", e);
	    return DB_RESULT.SELECT_FAILED;
	}
	
	try {
	    SQLiteStatement st = db.prepare("SELECT " + query);
	    while (st.step()) {
		for (int col = 0; col < st.columnCount(); col++) {
		    System.out.println(st.columnString(col));
		}
	    }
	} catch (SQLiteException e) {
	    log.error("Error performing query: SELECT " + query, e);
	    return DB_RESULT.SELECT_FAILED;
	}

	return DB_RESULT.SELECT_OK;
    }
    
    public SQLiteStatement select(String query) {
	if (query.toLowerCase().startsWith("select ")) {
	    query = query.substring(7);
	}
	File dbf = new File(dbFile);
	SQLiteConnection db = new SQLiteConnection(dbf);
	try {
	    db.open();
	} catch (SQLiteException e) {
	    log.error("Error while opening database for selection", e);
	    return null;
	}
	
	try {
	    return db.prepare("SELECT " + query);
	} catch (SQLiteException e) {
	    log.error("Error performing query: SELECT " + query, e);
	    return null;
	}
    }
}
